﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Unit;
using System.IO;
using System.Xml;
using Security;
using System.Web;
using System.Drawing;
using BarcodeLib.Barcode.QRCode;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Logic
{
    public class Certificate
    {
        public static ExceptionHandle m_ExceptionHandle = new ExceptionHandle();
        public string m_XMLContent;
        public Certificate()
        {
            m_XMLContent = "";
        }
        public Certificate(Stream FileContent)
        {
            m_XMLContent = LoadCertificate(FileContent, "");
        }
        public Certificate(Stream FileContent, string Key)
        {
            m_XMLContent = LoadCertificate(FileContent, Key);
        }
        public string GetCerNum()
        {
            try
            {
                if (m_XMLContent == "")
                {
//                     MessageBox.Show("Please Load Certificate File First!");
                    return "";
                }
                else
                {
                    XmlDocument XD = new XmlDocument();
                    XD.LoadXml(m_XMLContent);
                    return XD.DocumentElement.SelectSingleNode("CerNum").InnerText;
                }
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "";
            }
        }
        public string GetPrivateKey()
        {
            try
            {
                if (m_XMLContent == "")
                {
//                     MessageBox.Show("Please Load Certificate File First!");
                    return "";
                }
                else
                {
                    XmlDocument XD = new XmlDocument();
                    XD.LoadXml(m_XMLContent);
                    return XD.DocumentElement.SelectSingleNode("PrivateKey").InnerXml;
                }
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "";
            }
        }
        public string LoadCertificate(Stream FileContent, string CerKey)
        {
            if (FileContent == null)
                m_ExceptionHandle.Handle(new ArgumentException("Cannot Load Certificate From Stream"));
            else
            {
                try
                {
                    byte[] FSC = new byte[FileContent.Length];
                    FileContent.Read(FSC, 0, FSC.Length);
                    string FSCStr = Bytes.ToString(FSC, Encoding.ASCII);
                    XmlDocument XDBase = new XmlDocument();
                    XDBase.LoadXml(FSCStr);
                    XmlElement XD = XDBase.DocumentElement;
                    string CertificateCipherText = XD.SelectSingleNode("CipherText").InnerText;
                    //密文段
                    string MD5Attr = ((XmlElement)XD.SelectSingleNode("MD5")).GetAttribute("Flag");
                    string CertificateMD5Text = XD.SelectSingleNode("MD5").InnerText;
                    string CertificatePlainText = "";
                    //MD5校验段
                    if (MD5Attr == "True")
                    {
                        string MD5Key = MD5.HexCompute(CerKey, Encoding.Unicode);
                        CertificatePlainText = EasyTripleDES.HexDecrypt3DES(CertificateCipherText, MD5Key);
                    }else{
                        CertificatePlainText = EasyTripleDES.HexDecrypt3DES(CertificateCipherText, CertificateMD5Text);
                    }
                    if (!MD5.Exam(CertificatePlainText, Encoding.Unicode, CertificateMD5Text))
                    {
                        m_ExceptionHandle.Handle(new ArgumentException("Key ERROR Or Certificate File ERROR!"));
                        return "";
                    }
                    m_XMLContent = CertificatePlainText;
                    return CertificatePlainText;
                }
                catch (System.Xml.XmlException)
                {
                    m_ExceptionHandle.Handle(new ArgumentException("Error Certificate File.\nXml String Read Failed."));
                    return "";
                }
                catch (System.Exception ex)
                {
                    m_ExceptionHandle.Handle(ex);
                    return "";
                }
            }
            return "";
        }
        public static Stream MakeCer(string CerNum, string RSAXmlKey)
        {
            try
            {
                string XmlKey = RSAXmlKey;
                XmlDocument XmlCerPlaintext = new XmlDocument();
                XmlElement XCRoot = XmlCerPlaintext.CreateElement("Certificate");
                XmlElement XCAcc = XmlCerPlaintext.CreateElement("CerNum");
                XCAcc.InnerText = CerNum;
                XmlElement XCPK = XmlCerPlaintext.CreateElement("PrivateKey");
                XCPK.InnerXml = XmlKey;
                XCRoot.AppendChild(XCAcc);
                XCRoot.AppendChild(XCPK);
                XmlCerPlaintext.AppendChild(XCRoot);
                string CerPT = XmlCerPlaintext.OuterXml;
                string CerPTMD5 = MD5.HexCompute(CerPT, Encoding.Unicode);
                string CerCT = "";
                XmlDocument XmlCer = new XmlDocument();
                XmlElement XCR = XmlCer.CreateElement("Cer");
                XmlElement XCCT = XmlCer.CreateElement("CipherText");
                XmlElement XCMD5 = XmlCer.CreateElement("MD5");
                XCMD5.SetAttribute("Flag", "False");
                CerCT = EasyTripleDES.HexEncrypt3DES(CerPT, CerPTMD5);
                XCCT.InnerText = CerCT;
                XCMD5.InnerText = CerPTMD5;
                XCR.AppendChild(XCCT);
                XCR.AppendChild(XCMD5);
                XmlCer.AppendChild(XCR);
                MemoryStream ReMS = new MemoryStream();
                XmlCer.Save(ReMS);
                return ReMS;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return null;
            }
        }
    }

    public class Cheque
    {
//<Cheque>
//<ChequeNum>0123456789</ChequeNum>
//<Variation>Order</Variation>
//<Payee>0123456789123456789</Payee>
//<Payer>0123456789123456789</Payer>
//<AOC>20000.00</AOC>
//<Date>2010/8/30</Date>
//<DateValidUntil>2016/8/30</DateValidUntil>
//<Usage>Test</Usage>
//</Cheque> 

//<ChequeFile>
//<Ciphertext>
//</Ciphertext>
//<Plaintext>
//</Plaintext>
//</ChequeFile>
        public static ExceptionHandle m_ExceptionHandle = new ExceptionHandle();
        protected static string ChequeFileStr;
        public Cheque(Stream Filecontent)
        {
            try
            {
                ChequeFileStr = LoadCheque(Filecontent);
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
            }
        }
        public static string LoadCheque(Stream Filecontent)
        {
            try
            {
                if (Filecontent == null)
                {
                    m_ExceptionHandle.Handle(new System.ArgumentException());
                    return "<Exception>Filestream ERROR!</Exception>";
                }
                Stream FS = Filecontent;
                byte[] FSContent = new byte[FS.Length];
                FS.Read(FSContent, 0, FSContent.Length);
                int ChequeStart = -1;
                for (int i = FSContent.Length; i > 2; i-- )
                {
                    if (FSContent[i-2] == 0xFF && FSContent[i - 1] == 0xD9)
                    {
                        ChequeStart = i;
                        break;
                    }
                }
                if (ChequeStart == -1)
                {
                    m_ExceptionHandle.Handle(new System.ArgumentException());
                    return "<Exception>File Format Error!</Exception>";
                }
                byte[] bChequeInfo = Bytes.GetBytes(FSContent, ChequeStart, FSContent.Length - ChequeStart);
                string ChequeInfoStr = Bytes.ToString(bChequeInfo, Encoding.ASCII);
                ChequeFileStr = ChequeInfoStr;
                return ChequeInfoStr;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "<Exception>" + ex.Message.ToString() + "</Exception>";
            }
        }
        public int Verify()
        {
            try
            {
                string PayerAcc = GetPayerAccount();
                string CmdStr = "SELECT PUB_KEY FROM CERTIFICATES,PERSON_ACCOUNT WHERE ACCOUNT_ID='" + PayerAcc + "' AND PERSON_ID=PID AND [STATE]=TRUE";
                DataSet DS = new DataSet();
                DS = Logic.QueryData(CmdStr);
                string Pubkey = DS.Tables[0].Rows[0][0].ToString();
                bool FileCrypto =  DotNetRSA.HexVerify(GetCipherText(), Pubkey, GetPlainText(), Encoding.ASCII);
                if (!FileCrypto) return -1;
                CmdStr = "SELECT STATE FROM CHEQUE_INFO WHERE CHEQUE_ID=" + GetChequeNum();
                DS = Logic.QueryData(CmdStr);
                bool bChequeNum = (DS.Tables[0].Rows.Count == 1);
                if (bChequeNum)
                {
                    int nRe = Convert.ToInt32(DS.Tables[0].Rows[0][0].ToString());
                    return nRe;
                }
                return -1;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return -1;
            }
        }
        public string GetPlainText()
        {
            try
            {
                XmlDocument XD = new XmlDocument();
                XD.LoadXml(ChequeFileStr);
                XmlElement XE = XD.DocumentElement;
                return XE.SelectSingleNode("Plaintext").InnerXml;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "";
            }
        }
        public string GetCipherText()
        {
            try
            {
                XmlDocument XD = new XmlDocument();
                XD.LoadXml(ChequeFileStr);
                XmlElement XE = XD.DocumentElement;
                return XE.SelectSingleNode("Ciphertext").InnerText;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "";
            }
        }
        public string GetPayerAccount()
        {
            try
            {
                XmlDocument XD = new XmlDocument();
                XD.LoadXml(GetPlainText());
                XmlElement XE = XD.DocumentElement;
                return XE.SelectSingleNode("Payer").InnerText;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "";
            }
        }
        public string GetChequeNum()
        {
            try
            {
                XmlDocument XD = new XmlDocument();
                XD.LoadXml(GetPlainText());
                XmlElement XE = XD.DocumentElement;
                return XE.SelectSingleNode("ChequeNum").InnerText;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "";
            }
        }
        public bool IsInsurance()
        {
            try
            {
                XmlDocument XD = new XmlDocument();
                XD.LoadXml(GetPlainText());
                XmlElement XE = XD.DocumentElement;
                string InsuranceStr = XE.SelectSingleNode("Insurance").InnerText;
                if (InsuranceStr == "TRUE")
                    return true;
                else
                    return false;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return false;
            }
        }
        public bool IsOrdered()
        {
            try
            {
                XmlDocument XD = new XmlDocument();
                XD.LoadXml(GetPlainText());
                XmlElement XE = XD.DocumentElement;
                string VariationStr = XE.SelectSingleNode("Variation").InnerText;
                if (VariationStr == "Order")
                    return true;
                else
                    return false;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return false;
            }
        }
        public static string MakeChequeString(
            string ChequeNum, 
            string Variation, 
            string Payee, 
            string Payer, 
            string AOC, 
            string Date, 
            string DateUntil, 
            string UsageStr,
            string InsuranceStr)
        {
//<Cheque>
//<ChequeNum>0123456789</ChequeNum>
//<Variation>Order</Variation>
//<Payee>0123456789123456789</Payee>
//<Payer>0123456789123456789</Payer>
//<AOC>20000.00</AOC>
//<Date>2010/8/30</Date>
//<DateValidUntil>2016/8/30</DateValidUntil>
//<Usage>Test</Usage>
//<Insurance>True</Insurance>
//</Cheque> 
            return "<Cheque>" + 
                Logic.XmlExport("ChequeNum", ChequeNum) +
                Logic.XmlExport("Variation", Variation) +
                Logic.XmlExport("Payee", Payee) +
                Logic.XmlExport("Payer", Payer) +
                Logic.XmlExport("AOC", AOC) +
                Logic.XmlExport("Date", Date) +
                Logic.XmlExport("DateValidUntil", DateUntil) +
                Logic.XmlExport("Usage", UsageStr) +
                Logic.XmlExport("Insurance", InsuranceStr) +
                "</Cheque>";

        }
        public static byte[] MakeCheque(string ChequeStr, string PrivateKey, string BaseJPG)
        {
            try
            {
                XmlDocument XmlCD = new XmlDocument();
                XmlCD.LoadXml(ChequeStr);
                XmlElement XmlCF = XmlCD.DocumentElement;
                string ChequeNum = XmlCF.SelectSingleNode("ChequeNum").InnerText;
                string Variation = XmlCF.SelectSingleNode("Variation").InnerText;
                string PayerAccount = XmlCF.SelectSingleNode("Payer").InnerText;
                string PayerName = Logic.QueryAccount(PayerAccount);
                if (PayerName == "")
                {
                    m_ExceptionHandle.Handle(new System.ArgumentException("PayerAccount INVALID!"));
                    return null;
                }
                string PayeeAccount = "";
                string AOC = XmlCF.SelectSingleNode("AOC").InnerText;
                string Date = XmlCF.SelectSingleNode("Date").InnerText;
                string DateValidUntil = XmlCF.SelectSingleNode("DateValidUntil").InnerText;
                string UsageStr = XmlCF.SelectSingleNode("Usage").InnerText;
                string bInsurance = XmlCF.SelectSingleNode("Insurance").InnerText;
                string PayeeName = "";
                switch (Variation)
                {
                    case "Order":
                        {
                            PayeeAccount = XmlCF.SelectSingleNode("Payee").InnerText;
                            PayeeName = Logic.QueryAccount(PayeeAccount);
                            if (PayeeName == "")
                            {
                                m_ExceptionHandle.Handle(new ArgumentException("Payee Account INVALID!"));
                                return null;
                            }
                            break;
                        }
                    case "Bearer":
                        {
                            break;
                        }
                    default:
                        {
                            m_ExceptionHandle.Handle(new ArgumentException("Cheque Info Failed!"));
                            return null;
                        }
                }
                //生成附加信息
                XmlDocument CQFile = new XmlDocument();
                XmlElement CQFileRoot = CQFile.CreateElement("ChequeFile");
                XmlElement CQCT = CQFile.CreateElement("Ciphertext");
                byte[] bPT = Bytes.GetBytes(ChequeStr, Encoding.ASCII);
                byte[] bCT = DotNetRSA.Sign(bPT, PrivateKey);
                CQCT.InnerText = Bytes.ReadBytes(bCT);
                CQFileRoot.AppendChild(CQCT);
                XmlElement CQPT = CQFile.CreateElement("Plaintext");
                CQPT.InnerXml = ChequeStr;
                CQFileRoot.AppendChild(CQPT);
                CQFile.AppendChild(CQFileRoot);
                string AddInfo = CQFile.OuterXml;
                //
                if (!File.Exists(BaseJPG))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("BaseJPG ERROR!"));
                    return null;
                }
                Image CQBase = Image.FromFile(BaseJPG);
                using (Graphics g = Graphics.FromImage(CQBase))
                {
                    //<QRCode><ChequeNum/><PayerAccount/></QRCode>
                    XmlDocument QCXml = new XmlDocument();
                    XmlElement QCRoot = QCXml.CreateElement("QRCode");
                    XmlElement QCCN = QCXml.CreateElement("ChequeNum");
                    QCCN.InnerText = ChequeNum;
                    QCRoot.AppendChild(QCCN);
                    XmlElement QCPA = QCXml.CreateElement("PlayerAccount");
                    QCPA.InnerText = PayerAccount;
                    QCRoot.AppendChild(QCPA);
                    QCXml.AppendChild(QCRoot);
                    MemoryStream QCms = new MemoryStream();
                    //加入barcode
                    QRCode QC = new QRCode();
                    QC.ModuleSize = 3;
                    QC.Data = QCXml.OuterXml;
                    QC.drawBarcode(QCms);
                    QC.Encoding = QRCodeEncoding.Auto;
                    QC.Version = QRCodeVersion.Auto;
                    QC.ECL = ErrorCorrectionLevel.M;
                    Image QCImg = Image.FromStream(QCms);
                    //
                    g.DrawImage(CQBase, 0, 0, CQBase.Width, CQBase.Height);
                    g.DrawImage(QCImg, 300, 100, 90, 90);
                    using (Font f = new Font(@"微软雅黑", 11.0f))
                    {
                        using (Brush b = new SolidBrush(Color.Gray))
                        {
                            g.DrawString(ChequeNum, f, b, 215, 15);
                            g.DrawString(PayerAccount, f, b, 215, 34);
                            g.DrawString(Date, f, b, 205, 132);
                            g.DrawString(DateValidUntil, f, b, 205, 150);
                        }
                    }
                    using (Font f = new Font(@"微软雅黑", 11.0f, FontStyle.Bold))
                    {
                        using (Brush b = new SolidBrush(Color.FromArgb(30, 30, 128)))
                        {
                            g.DrawString(PayerName, f, b, 70, 132);
                            g.DrawString(PayeeName, f, b, 70, 150);
                            g.DrawString(PayeeAccount, f, b, 13, 170);
                        }
                        using (Brush b = new SolidBrush(Color.Red))
                        {
                            if (bInsurance == "TRUE")
                            {
                                g.DrawString("保付", f, b, 252, 94);
                            }
                        }
                    }
                    using(Font f = new Font(@"微软雅黑", 18.0f, FontStyle.Bold))
                    {
                        using (Brush b = new SolidBrush(Color.FromArgb(30, 30, 128)))
                        {
                            PointF pf = new PointF(225, 85);
                            StringFormat sf = new StringFormat();
                            sf.Alignment = StringAlignment.Far;
                            g.DrawString(AOC, f, b, pf, sf);
                        }
                    }
                }
                MemoryStream ms = new MemoryStream();
                CQBase.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
                ms.Position = 0;
                byte[] bResult = new byte[ms.Length];
                ms.Read(bResult, 0, bResult.Length);
                byte[] bAddInfo = Bytes.GetBytes(AddInfo, Encoding.ASCII);
                byte[] ChqFileJPEG = Bytes.LinkBytes(bResult, bAddInfo);
                return ChqFileJPEG;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return null;
            }
        }
        public void SaveChequeFile(string Filename, byte[] Filecontent)
        {
            try
            {
                if (File.Exists(Filename))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("File has ALREADY existed!"));
                    return;
                }
                FileStream FSSave = new FileStream(Filename, FileMode.Create);
                FSSave.Write(Filecontent, 0, Filecontent.Length);
                FSSave.Close();
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
            }
        }
    }
    public class Logic
    {
        public static string tempConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Sylvanjeo\Desktop\花期项目\江凡\EasyCheque\EasyCheque\App_Data\EasyCheque.accdb";
        public static ExceptionHandle m_ExceptionHandle = new ExceptionHandle();
        protected static string m_BaseJPG;
        public static void GetChequeBase(string BaseJPG)
        {
            m_BaseJPG = BaseJPG;
        }
        public static string XmlExport(string Node, string InnerText, params string[] NodeAttribute)
        {
            try
            {
	            XmlDocument XmlResult = new XmlDocument();
	            XmlElement XmlReElem = XmlResult.CreateElement(Node);
                for (int i = 0; i < NodeAttribute.Length; i++ )
                {
                    string[] NA = NodeAttribute[i].Split(new string[] { ":" }, StringSplitOptions.None);
                    XmlReElem.SetAttribute(NA[0], NA[1]);
                }
	            XmlReElem.InnerText = InnerText;
	            XmlResult.AppendChild(XmlReElem);
	            return XmlResult.OuterXml;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "<Exception>" + ex.Message.ToString() + "</Exception>";
            }
        }
        public static string MessageExport(string MsgType, string InnerText)
        {
            try
            {
                return XmlExport("Message", InnerText, "MsgType:" + MsgType);
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "<Exception>" + ex.Message.ToString() + "</Exception>";
            }
        }
        public static string QueryAccount(string AccountStr)
        {
            try
            {
                string CmdStr = "SELECT C_NAME FROM PERSON_INFO, ACCOUNT_INFO WHERE ID = ACCOUNT_INFO.PERSON AND ACCOUNT_INFO.ACCOUNT_ID = '" + AccountStr + "'";
                DataSet DSR = QueryData(CmdStr);
                if (DSR.Tables[0].Rows.Count != 1)
                {
                    return "";
                }else{
                    return DSR.Tables[0].Rows[0][0].ToString();
                }
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "";
            }
        }
        public static string LogIn(Stream FileContent, string Key)
        {
            try
            {
                Certificate CFS = new Certificate(FileContent);
                if (CFS.m_XMLContent == "")
                {
                    return XmlExport("LogInResult", @"证书错误", "Result:Failed");
                }
                string CerNumStr = CFS.GetCerNum();
                string CryptAccountStr = DotNetRSA.HexSign(CerNumStr, Encoding.ASCII, CFS.GetPrivateKey());
                string CmdStr = "SELECT PUB_KEY, PID FROM CERTIFICATES WHERE [STATE]=TRUE AND C_ID = " + CerNumStr;
                DataSet DS = Logic.QueryData(CmdStr);
                if (DS.Tables[0].Rows.Count != 1)
                {
                    return XmlExport("LogInResult", "无效证书", "Result:Failed");
                }
                string PublicXmlKey = DS.Tables[0].Rows[0][0].ToString();
                //
                bool bResult = Security.DotNetRSA.HexVerify(CryptAccountStr, PublicXmlKey, CerNumStr, Encoding.ASCII);
                if (bResult)
                {
                    CmdStr = "SELECT PASSWORD FROM CERTIFICATES,PERSON_INFO WHERE ID=PID AND C_ID=" + CerNumStr;
                    DS = Logic.QueryData(CmdStr);
                    if (DS.Tables[0].Rows.Count != 1)
                    {
                        return XmlExport("LogInResult", "内部数据库错误", "Result:Failed");
                    }
                    else
                    {
                        if (!MD5.Exam(Key, Encoding.ASCII, DS.Tables[0].Rows[0][0].ToString()))
                        {
                            return XmlExport("LogInResult", "密码错误", "Result:Failed");
                        }
                    }
                    CmdStr = @"SELECT PID FROM CERTIFICATES WHERE C_ID=" + CerNumStr;
                    DS = QueryData(CmdStr);
                    if (DS.Tables[0].Rows.Count != 1)
                    {
                        return XmlExport("LogInResult", "内部数据库错误", "Result:Failed");
                    }
                    return XmlExport("LogInResult", DS.Tables[0].Rows[0][0].ToString(), "Result:Success");
                }
                else
                {
                    return XmlExport("LogInResult", "数字签名错误", "Result:Failed");
                }
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "";
            }
        }
        public static Stream RegCer(string P_ID)
        {
            try
            {
                string CmdStr = @"SELECT C_ID FROM CERTIFICATES WHERE PID = " + P_ID;
                DataSet DS = QueryData(CmdStr);
                if (DS.Tables[0].Rows.Count != 0)
                {
                    CmdStr = @"UPDATE CERTIFICATES SET STATE=FALSE WHERE PID=" + P_ID;
                    UpdateData(CmdStr);
                }
                string RSAKeyStr = DotNetRSA.GenerateKey();
                CmdStr = @"INSERT INTO CERTIFICATES(PUB_KEY,PID) VALUES('" + DotNetRSA.XmlPublickey(RSAKeyStr) + "'," + P_ID + ")";
                if (UpdateData(CmdStr))
                {
                    CmdStr = @"SELECT C_ID FROM CERTIFICATES WHERE STATE=TRUE AND PID = " + P_ID;
                    DS = QueryData(CmdStr);
                    if (DS.Tables[0].Rows.Count != 1)
                    {
                        m_ExceptionHandle.Handle(new ArgumentException("DataBase ERROR"));
                        return null;
                    }
                    string CerNum = DS.Tables[0].Rows[0][0].ToString();
                    return Certificate.MakeCer(CerNum, DotNetRSA.XmlPrivatekey(RSAKeyStr));
                }
                m_ExceptionHandle.Handle(new ArgumentException("DataBase ERROR"));
                return null;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return null;
            }
        }
        public static Stream MakeCheque(
            ref string refChequeNum,
            string PayerAcc, 
            string PayeeAcc, 
            string AOC, 
            string DeadLine, 
            bool Varition, 
            bool Insurance, 
            string UsageStr,
            string PrivateKey, 
            string ChequeKey)
        {
            try
            {
                string CmdStr = "";
                DataSet DS = new DataSet();
                CmdStr = @"SELECT PERSON_ID FROM PERSON_ACCOUNT WHERE ACCOUNT_ID='" + PayerAcc + "'";
                DS = QueryData(CmdStr);
                string PayerID = DS.Tables[0].Rows[0][0].ToString();
                if (!Varition)
                {
                    PayeeAcc = "NULL";
                }else{
                    CmdStr = @"SELECT PERSON FROM ACCOUNT_INFO WHERE ACCOUNT_ID='" + PayeeAcc + "'";
                    DS = QueryData(CmdStr);
                    if (DS.Tables[0].Rows.Count == 1)
                    {
                        PayeeAcc = "'" + PayeeAcc + "'";
                    }else{
                        m_ExceptionHandle.Handle(new ArgumentException("Account NOT Exists!"));
                        return null;
                    }
                }
                CmdStr = @"SELECT [MONEY] FROM ACCOUNT_INFO WHERE ACCOUNT_ID='" + PayerAcc + "'";
                DS = QueryData(CmdStr);
                double dAOC = Convert.ToDouble(AOC);
                double Useable = Convert.ToDouble(DS.Tables[0].Rows[0][0].ToString());
                if (Useable < dAOC)
                {
                    System.ArgumentException AE = new System.ArgumentException("NOT Enough Money!");
                    m_ExceptionHandle.Handle(AE);
                    return null;
                }
                CmdStr = @"INSERT INTO CHEQUE_INFO([PAY_ID],[ACCOUNT_ID],[MONEY],[RECV_ACC],[PASSWORD],[INSURANCE],[DEADLINE])";
                string ChequeKeyMD5 = Security.MD5.HexCompute(ChequeKey, Encoding.ASCII);
                string VaritionStr = "";
                if (Varition)
                    VaritionStr = "Order";
                else
                    VaritionStr = "Bearer";
                string InsuranceStr;
                if (Insurance)
                    InsuranceStr = "TRUE";
                else
                    InsuranceStr = "FALSE";
                CmdStr += @" VALUES(" + PayerID + ",'" + PayerAcc + "'," + AOC + "," + PayeeAcc + ",'" + ChequeKeyMD5 + "'," + InsuranceStr + ",'" + DeadLine + "')";
                string AN = "";
                if (!InsertData(CmdStr, "CHEQUE_INFO", ref AN))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("DataBase ERROR!"));
                    return null;
                }
                if (Insurance)
                {
                    CmdStr = @"UPDATE ACCOUNT_INFO SET USEABLE = USEABLE + " + AOC + ",[MONEY]=[MONEY] - " + AOC + " WHERE ACCOUNT_ID = '" + PayerAcc + "'";
                    if (!UpdateData(CmdStr))
                    {
                        m_ExceptionHandle.Handle(new ArgumentException("Update ERROR!"));
                        return null;
                    }
                }
                long lAN = Convert.ToInt32(AN);
                string ChequeNum = string.Format("{0:0000000000}", lAN);
                PayeeAcc = PayeeAcc.Substring(1, PayeeAcc.Length - 2);
                string XmlCheque = Cheque.MakeChequeString(ChequeNum, VaritionStr, PayeeAcc, PayerAcc, AOC, DateTime.Now.ToString("yyyy/MM/dd"), DeadLine, UsageStr, InsuranceStr);
                byte[] bRe = Cheque.MakeCheque(XmlCheque, PrivateKey, m_BaseJPG);
                MemoryStream MSRe = new MemoryStream(bRe);
                refChequeNum = ChequeNum;
                return MSRe;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return null;
            }
        }
        public static string UseCheque(Stream Filecontent, string Key, string PayeeAccount)
        {
            try
            {
                Cheque CQFile = new Cheque(Filecontent);
                if (CQFile.Verify() == -1)
                {
                    m_ExceptionHandle.Handle(new ArgumentException("INVALIDE ChequeFile"));
                    return "无效的证书";
                }
                string CmdStr = "";
                string PayerAcc = "";
                string AOC = "";
                DataSet DS;
                CmdStr = @"SELECT [PASSWORD] FROM CHEQUE_INFO WHERE CHEQUE_ID=" + CQFile.GetChequeNum();
                DS = QueryData(CmdStr);
                if (DS.Tables[0].Rows[0][0].ToString() != MD5.HexCompute(Key, Encoding.ASCII))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("Key ERROR!"));
                    return "密码错误";
                }
                CmdStr = @"SELECT STATE FROM CHEQUE_INFO WHERE CHEQUE_ID=" + CQFile.GetChequeNum();
                DS = QueryData(CmdStr);
                string ChequeState = DS.Tables[0].Rows[0][0].ToString();
                if (ChequeState != "0")
                {
                    if (ChequeState == "1")
                    {
                        m_ExceptionHandle.Handle(new ArgumentException("USED CHEQUE!"));
                        return "支票已经被使用";
                    }else{
                        m_ExceptionHandle.Handle(new ArgumentException("INVALID CHEQUE!"));
                        return "支票已经作废";
                    }
                }
                if (CQFile.IsOrdered())
                {
                    CmdStr = @"SELECT RECV_ACC FROM CHEQUE_INFO WHERE CHEQUE_ID = " + CQFile.GetChequeNum();
                    DS = QueryData(CmdStr);
                    if (DS.Tables[0].Rows.Count != 1)
                    {
                        m_ExceptionHandle.Handle(new ArgumentException("INVALID PayeeAccount!"));
                        return "收票账户不存在";
                    }
                    PayeeAccount = DS.Tables[0].Rows[0][0].ToString();
                }else{
                    CmdStr = @"SELECT * FROM ACCOUNT_INFO WHERE ACCOUNT_ID = '" + PayeeAccount + "'";
                    DS = QueryData(CmdStr);
                    if (DS.Tables[0].Rows.Count != 1)
                    {
                        m_ExceptionHandle.Handle(new ArgumentException("INVALID PayeeAccount!"));
                        return "收票账户不存在";
                    }
                }
                CmdStr = @"SELECT ACCOUNT_ID FROM CHEQUE_INFO WHERE CHEQUE_ID = " + CQFile.GetChequeNum();
                DS = QueryData(CmdStr);
                PayerAcc = DS.Tables[0].Rows[0][0].ToString();
                CmdStr = @"SELECT [MONEY] FROM CHEQUE_INFO WHERE CHEQUE_ID = " + CQFile.GetChequeNum();
                DS = QueryData(CmdStr);
                AOC = DS.Tables[0].Rows[0][0].ToString();
                //空头支票检查
                double dAOC = Convert.ToDouble(AOC);
                if (CQFile.IsInsurance())
                {
                    CmdStr = @"SELECT USEABLE FROM ACCOUNT_INFO WHERE ACCOUNT_ID='" + PayerAcc + "'";
                }else{
                    CmdStr = @"SELECT [MONEY] FROM ACCOUNT_INFO WHERE ACCOUNT_ID='" + PayerAcc + "'";
                }
                DS = QueryData(CmdStr);
                if (Convert.ToDouble(DS.Tables[0].Rows[0][0].ToString()) < dAOC)
                {
                    m_ExceptionHandle.Handle(new ArgumentException("INVALID MONEY!"));
                    return "空头支票";
                }
                if (CQFile.IsInsurance())
                {
                    CmdStr = @"UPDATE ACCOUNT_INFO SET USEABLE=USEABLE-" + AOC + " WHERE ACCOUNT_ID='" + PayerAcc + "'";
                }else{
                    CmdStr = @"UPDATE ACCOUNT_INFO SET [MONEY]=[MONEY]-" + AOC + " WHERE ACCOUNT_ID='" + PayerAcc + "'";
                }
                if (!UpdateData(CmdStr))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("UPDATE ERROR!"));
                    return "数据库更新错误";
                }
                CmdStr = @"UPDATE ACCOUNT_INFO SET [MONEY]=[MONEY]+" + AOC + " WHERE ACCOUNT_ID='" + PayeeAccount + "'";
                if (!UpdateData(CmdStr))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("UPDATE ERROR!"));
                    return "数据库更新错误";
                }
                CmdStr = @"UPDATE CHEQUE_INFO SET [STATE]=1 WHERE CHEQUE_ID=" + CQFile.GetChequeNum();
                if (!UpdateData(CmdStr))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("UPDATE ERROR!"));
                    return "数据库更新错误";
                }
                CmdStr = @"INSERT INTO TRANS_DETAIL([ACCOUNT],[DECREASE],[TYPE]) VALUES('" + PayerAcc + "'," + AOC + ",'电子支票')";
                if (!UpdateData(CmdStr))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("UPDATE ERROR!"));
                    return "数据库更新错误";
                }
                CmdStr = @"INSERT INTO TRANS_DETAIL([ACCOUNT],[INCREASE],[TYPE]) VALUES('" + PayeeAccount + "'," + AOC + ",'电子支票')";
                if (!UpdateData(CmdStr))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("UPDATE ERROR!"));
                    return "数据库更新错误";
                }
                return "";
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "未知错误";
            }
        }
        public static string DeleteCheque(string ChequeNum)
        {
            try
            {
                string CmdStr = @"SELECT [STATE] FROM CHEQUE_INFOR WHERE CHEQUE_ID=" + ChequeNum;
                DataSet DS = QueryData(CmdStr);
                if (DS.Tables[0].Rows.Count != 1)
                {
                    m_ExceptionHandle.Handle(new ArgumentException("INVALID ChequeNum!"));
                    return "无法识别的支票号";
                }
                CmdStr = @"UPDATE CHEQUE_INFO SET [STATE]=2 WHERE CHEQUE_ID=" + ChequeNum;
                if (!UpdateData(CmdStr))
                {
                    m_ExceptionHandle.Handle(new ArgumentException("Update ERROR!"));
                    return "更新数据时发生错误";
                }
                return "";
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return "未知错误";
            }
        }
        protected static string g_ConnStr = "";
        public static bool LinkDataBase(string ConnStr)
        {
            try
            {
                g_ConnStr = ConnStr; 
                return true;
            }
            catch (System.Exception ex)
            {
            	m_ExceptionHandle.Handle(ex);
                return false;
            }
        }
        public static DataSet QueryData(string CommandStr)
        {
            try
            {
                OleDbConnection ODC = new OleDbConnection(g_ConnStr);
                ODC.Open();
                //
                OleDbDataAdapter ODDA = new OleDbDataAdapter(CommandStr, ODC);
                DataSet DS = new DataSet();
                ODDA.Fill(DS);
                //
                ODC.Close();
                return DS;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return null;
            }
        }
        public static bool UpdateData(string CmdStr)
        {
            try
            {
                OleDbConnection ODC = new OleDbConnection(g_ConnStr);
                ODC.Open();
                //
                OleDbCommand ODCmd = new OleDbCommand(CmdStr, ODC);
                int nRows = ODCmd.ExecuteNonQuery();
                //
                ODC.Close();
                if (nRows > 0)
                    return true;
                else
                    return false;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return false;
            }
        }
        public static bool InsertData(string InsertStr, string TableName, ref string AutoNum)
        {
            try
            {
                OleDbConnection ODC = new OleDbConnection(g_ConnStr);
                ODC.Open();
                //
                OleDbCommand ODCmd = new OleDbCommand(InsertStr, ODC);
                OleDbDataAdapter ODDA = new OleDbDataAdapter(@"SELECT @@IDENTITY FROM " + TableName, ODC);
                int nRows = ODCmd.ExecuteNonQuery();
                DataSet DS = new DataSet();
                ODDA.Fill(DS);
                AutoNum = DS.Tables[0].Rows[0][0].ToString();
                //
                ODC.Close();
                if (nRows > 0)
                    return true;
                else
                    return false;
            }
            catch (System.Exception ex)
            {
                m_ExceptionHandle.Handle(ex);
                return false;
            }
        }
    }
}
